/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package introse;

import com.sun.net.ssl.internal.ssl.Debug;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

/**
 *
 * @author Alfred
 */
public class productManager {

    private List<product> productList;

    public productManager() {
        loadProducts();
    }

    public productManager(Date selectedDate) {
        loadProductsWithStockAtDate(selectedDate);
    }
    
    public Boolean hasProduct(String productName){
        for(product prod : getAllProducts()){
            if(prod.getProductName().trim().toLowerCase().equals(productName.trim().toLowerCase())){
                return true;
            }
        }
        return false;
    }

    public final Boolean loadProducts() {
        return loadProducts("SELECT p.`productNumber`, `productName`, `isGasul`, `startingStock`, `add`, `less`, `GuntherX`.price, `isActive`, `date`"
                + " FROM product p, productPriceHistory h"
                + " left join (select productPriceHistory.productNumber, price from productPriceHistory"
                + " left join (select max(dateAndTime) as `maxTime`, productNumber from productPriceHistory group by productNumber) as `Gunther` on productPriceHistory.productNumber = Gunther.productNumber"
                + " where dateAndTime = maxTime) as `GuntherX` on GuntherX.productNumber = h.productNumber"
                + " left join (select `inventory`.`productNumber`, `startingStock`, `add`, `less`, `inventory`.`dateAndTime` as `date` from inventory"
                + " left join(select max(dateAndTime) as dateAndTime, productNumber from inventory group by productNumber) as `Fabio` on `Fabio`.productNumber = inventory.productNumber"
                + " where `Fabio`.dateAndTime = inventory.dateAndTime) as `FabioX` on FabioX.productNumber = h.`productNumber`"
                + " WHERE p.productNumber = h.productNumber"
                + " GROUP BY (p.productNumber)"
                + " ORDER BY (h.dateAndTime)");
    }

    public final Boolean loadProductsWithStockAtDate(Date selectedDate) {
        String selectedDateString = new SimpleDateFormat("yyyy-MM-dd").format(selectedDate);
        return loadProducts(String.format("select `product`.`productNumber`, `productName`, `isGasul`, `startingStock`, `add`, `less`, `GuntherX`.price, `isActive`, `inventory`.`dateAndTime` as `date` from product"
                + " left join inventory on product.productNumber = inventory.productNumber"
                + " left join (select productPriceHistory.productNumber, price from productPriceHistory"
                + " left join (select max(dateAndTime) as `maxTime`, productNumber from productPriceHistory group by productNumber) as `Gunther` on productPriceHistory.productNumber = Gunther.productNumber"
                + " where dateAndTime = maxTime) as `GuntherX` on GuntherX.productNumber = product.productNumber"
                + " where `inventory`.`dateAndTime` = '%s'", selectedDateString));
    }

    public final Boolean loadProducts(String sqlStatement) {
        try {
            databaseConn conn = new databaseConn();
            ResultSet products = conn.sqlRead(sqlStatement);

            List<product> tempProductList = new ArrayList();
            while (products.next()) {
                try {
                    tempProductList.add(new product(products.getInt("productNumber"), products.getString("productName"), products.getFloat("price"), products.getInt("startingStock"), products.getInt("add"), products.getInt("less"), products.getBoolean("isGasul"), products.getBoolean("isActive"), products.getDate("date")));
                } catch (Exception ex) {
                }
            }
            this.productList = tempProductList;

            return true;
        } catch (SQLException ex) {
            Debug.println("", ex.toString());
        }
        return false;
    }

    public List<product> getProducts(Boolean isActive) {
        List<product> tempProducts = new ArrayList();
        for (product prod : productList) {
            if (prod.getIsActive() == isActive) {
                tempProducts.add(prod);
            }
        }
        return tempProducts;
    }

    public List<product> getAllProducts() {
        return this.productList;
    }

    //Get product from product number
    public product getProduct(int productNumber) {
        for (product prod : productList) {
            if (prod.getProductNumber() == productNumber) {
                return prod;
            }
        }
        return null;
    }

    //Add a product
    public Boolean addProduct(String name, float price, boolean isGasul) {
        databaseConn conn = new databaseConn();
        conn.sqlStatement(String.format("INSERT INTO `sashmirgasul`.`product` (`productName`, `isGasul`, `isActive`) VALUES ('%s', %d, TRUE);", name, isGasul ? 1 : 0));

        ResultSet lastAddedProduct = conn.sqlRead("SELECT max(`productNumber`) FROM product");
        try {
            lastAddedProduct.next();
            int lastAddedProductNumber = lastAddedProduct.getInt(1);
            conn.sqlStatement(String.format("INSERT INTO `sashmirgasul`.`inventory` (`dateAndTime`, `productNumber`, `startingStock`, `add`, `less`) VALUES (NOW(), %d, 0, 0, 0);", lastAddedProductNumber));
            conn.sqlStatement(String.format("INSERT INTO `sashmirgasul`.`productPriceHistory` (`dateAndTime`, `productNumber`, `price`) VALUES (NOW(), %d, %f);", lastAddedProductNumber, price));


        } catch (SQLException ex) {
            Debug.println("", ex.toString());
        }


        loadProducts();
        return true;

    }
}
